drop table if exists logons;
drop table if exists user_roles;
drop table if exists users;
drop table if exists compositions;
drop table if exists composition_values;
drop table if exists vagons;
drop table if exists trips;
drop table if exists trip_values;
drop table if exists stations;
drop table if exists distances;

drop sequence if exists seq_user_roles;
drop sequence if exists seq_users;
drop sequence if exists seq_trips;
drop sequence if exists seq_stations;
drop sequence if exists seq_distances;
drop sequence if exists seq_logons;
drop sequence if exists seq_compositions;
drop sequence if exists seq_vagons;

drop schema if exists ctl;

commit;

create schema ctl;

create table ctl.logons (
	id long primary key not null,
	date_logon timestamp not null,
	user_id integer not null
	);
	
create table ctl.user_roles (
	id long primary key not null,
	role_name varchar(50) not null
	);
	
create table ctl.users (
	id long primary key not null,
	user_name varchar(200),
	user_logon varchar(50) not null,
	user_pass varchar(50) not null,
	user_role_id integer not null,
	date_valid_from timestamp,
	date_valid_until timestamp,
	active boolean not null default true,
	);
	
create table ctl.compositions (
	id long primary key not null,
	internal_id varchar(10) not null,
	active boolean not null default true,
	);
	
create table ctl.vagons (
	id long primary key not null,
	internal_id varchar(10) not null,
	composition_id long,
	rev_one_from timestamp,
	rev_one_until timestamp,
	rev_two_from timestamp,
	rev_two_until timestamp,
	revision_distance double,
	revision_correction double,
	active boolean not null default true,
	);
	
create table composition_values (
	composition_id long not null,
	vagon_id long not null
)

create table ctl.trips (
	id long primary key not null,
	station_from_id long,
	station_to_id long,
	date_from timestamp not null,
	date_until timestamp not null,
	distance_km double not null
	);
	
create table ctl.trip_values (
	trip_id long not null,
	composition_id long not null,
	vagon_id long not null,
	);
	
create table ctl.stations (
	id long primary key, 
	date_created timestamp not null,
	station_name varchar(200) not null,
	active boolean not null default true,
	);

commit;

create index logon_index on ctl.logons (id);
create index user_role_id_index on ctl.user_roles (id);
create index user_name_index on ctl.users (user_logon);
create index vagon_internal_id_index on ctl.vagons (internal_id);
create index compositions_internal_id_index on ctl.compositions (internal_id);

commit;

alter table ctl.users add constraint logon_unique unique (user_logon);
alter table ctl.vagons add constraint vagon_internal_id_unique unique (internal_id);
alter table ctl.compositions add constraint compositions_internal_id_unique unique (internal_id);
alter table ctl.stations add constraint station_name_unique unique (station_name);

commit;

alter table ctl.logons add foreign key (user_id) references ctl.users (id) on delete cascade on update cascade;
alter table ctl.users add foreign key (user_role_id) references ctl.user_roles (id);
alter table ctl.vagons add foreign key (composition_id) references ctl.compositions (id) on delete set null on update cascade;
alter table ctl.trips add foreign key (station_from_id) references ctl.stations (id) on delete set null on update cascade;
alter table ctl.trips add foreign key (station_to_id) references ctl.stations (id) on delete set null on update cascade;
alter table ctl.trips add foreign key (composition_id) references ctl.compositions (id) on delete cascade on update cascade;
alter table ctl.trip_values add foreign key (trip_id) references ctl.trips (id) on delete no action on update cascade;
alter table ctl.trip_values add foreign key (composition_id) references ctl.compositions (id) on delete no action on update cascade;
alter table ctl.trip_values add foreign key (vagon_id) references ctl.vagons (id) on delete no action on update cascade;

commit;

create sequence seq_logons;
create sequence seq_user_roles;
create sequence seq_users;
create sequence seq_trips;
create sequence seq_stations;
create sequence seq_compositions;
create sequence seq_vagons;

commit;

insert into ctl.user_roles values (0, 'admin');
insert into ctl.user_roles values (1, 'regular');
insert into ctl.user_roles values (2, 'reporter');
insert into ctl.users values(seq_users.nextval, 'Admin', 'admin', 'admin', 0, null, null, true);

commit;
